package org.morningangel.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import javax.naming.NamingException;

import org.morningangel.bean.Will;
import org.morningangel.dao.WillDao;
import org.morningangel.util.DBHelperUtil;

public class WillDaoImpl implements WillDao {

	@Override
	public Integer save(Will will) {
		Connection conn = null;
		PreparedStatement ps = null;
		Integer suNum = null;
		try {
			conn = DBHelperUtil.getConnection();
			//打开事务
			conn.setAutoCommit(false);
			String sql = "insert into tb_will (userId, userName, telNum, area, type, ideaTime, willContent, time) values (?, ?, ?, ?, ?, ?, ?, ?)";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, will.getUserId());
			ps.setString(2, will.getUserName());
			ps.setString(3, will.getTelNum());
			ps.setString(4, will.getArea());
			ps.setString(5, will.getType());
			ps.setString(6, will.getIdeaTime());
			ps.setString(7, will.getWillContent());
			ps.setTimestamp(8, new Timestamp(will.getTime().getTime()));
			suNum = ps.executeUpdate();
			//提交事务
			conn.commit();
			//关闭事务
			conn.setAutoCommit(true);
		} catch (NamingException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		DBHelperUtil.close(ps, conn);
		
		return suNum;
	}

	@Override
	public Integer update(Will will) {
		Connection conn = null;
		PreparedStatement ps = null;
		Integer suNum = null;
		try {
			conn = DBHelperUtil.getConnection();
			//打开事务
			conn.setAutoCommit(false);
			String sql = "update tb_will set userName = ?, telNum = ?, area = ?, type = ?, ideaTime = ?, willContent = ?, time = ? where id = ?";
			ps = conn.prepareStatement(sql);
			ps.setString(1, will.getUserName());
			ps.setString(2, will.getTelNum());
			ps.setString(3, will.getArea());
			ps.setString(4, will.getType());
			ps.setString(5, will.getIdeaTime());
			ps.setString(6, will.getWillContent());
			ps.setTimestamp(7, new Timestamp(will.getTime().getTime()));
			ps.setInt(8, will.getId());
			suNum = ps.executeUpdate();
			//提交事务
			conn.commit();
			//关闭事务
		} catch (NamingException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		DBHelperUtil.close(ps, conn);
		
		return suNum;
	}

	@Override
	public Will getById(Integer id) {
		Connection conn = null;
		PreparedStatement ps = null;
		Will will = null;
		ResultSet rs = null;
		try {
			conn = DBHelperUtil.getConnection();
			String sql = "select id, userId, userName, telNum, area, type, ideaTime, willContent, time from tb_will where id = ?";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			rs = ps.executeQuery();
			if (rs.next()) {
				will = new Will();
				setWill(rs, will);
			}
		} catch (NamingException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		DBHelperUtil.close(rs, ps, conn);
		
		return will;
	}

	@Override
	public List<Will> getByUserId(Integer userId) {
		Connection conn = null;
		PreparedStatement ps = null;
		List<Will> list =new ArrayList<Will>();
		ResultSet rs = null;
		try {
			conn = DBHelperUtil.getConnection();
			String sql = "select id, userId, userName, telNum, area, type, ideaTime, willContent, time from tb_will where userId = ?";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, userId);
			rs = ps.executeQuery();
			while (rs.next()) {
				Will  will = new Will();
				setWill(rs, will);
				list.add(will);
			}
		} catch (NamingException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		DBHelperUtil.close(rs, ps, conn);
		
		return list;
	}
	
	private void setWill(ResultSet rs, Will will) throws SQLException {
		will.setId(rs.getInt(1));
		will.setUserId(rs.getInt(2));
		will.setUserName(rs.getString(3));
		will.setTelNum(rs.getString(4));
		will.setArea(rs.getString(5));
		will.setType(rs.getString(6));
		will.setIdeaTime(rs.getString(7));
		will.setWillContent(rs.getString(8));
		will.setTime(new Date(rs.getTimestamp(9).getTime()));
	}

}
